package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import Model.Order;
import utils.DBHelper;

public class OderDao {
   Connection conn;
   String sql;
   PreparedStatement ps;
   ResultSet rs;
   public OderDao() {
	   conn = DBHelper.getConnection();
   }
   public ArrayList<Order> getAll(){
	   ArrayList<Order> list = new ArrayList<Order>();
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
	   		+ "t2.username as username,t1.food_id as food_id,"
	   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
	   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
	   		+ "t1.content as content,t1.comment_time as comment_time "
	   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
	   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id";
	   try {
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		while(rs.next()) {
			int order_id = rs.getInt("order_id");
			int user_id = rs.getInt("user_id");
			String username = rs.getString("username");
			int food_id = rs.getInt("food_id");
			String foodname = rs.getString("foodname");
			int num = rs.getInt("num");
			double sum = rs.getDouble("sum");
			Timestamp d1 = rs.getTimestamp("ordertime");
			Date ordertime = new Date(d1.getTime());
		
			String suggesttime = rs.getString("suggesttime");
			String content = rs.getString("content");
			Timestamp d2 = rs.getTimestamp("comment_time");
			Date comment_time = null;
			if(d2!=null)
			comment_time = new Date(d2.getTime());
			Order order = new Order(order_id,user_id,username,food_id,foodname,
					num,sum,ordertime,suggesttime,content,comment_time);
			list.add(order);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return list;
	   
   }
   public boolean insert(Order order) {
	   sql = "insert into "
	   		+ "tb_order(user_id,food_id,"
	   		+ "num,sum,ordertime,suggesttime,content,comment_time) values(?,?,?,?,?,?,?,?)";
	   try {
		ps = conn.prepareStatement(sql);
		 ps.setInt(1, order.getUser_id());
		   ps.setInt(2, order.getFood_id());
		   ps.setInt(3, order.getNum());
		   ps.setDouble(4, order.getSum());
		   Date d = new Date();
		   Timestamp date = new Timestamp(d.getTime());
		   ps.setTimestamp(5, date);
		   ps.setString(6, order.getSuggesttime());
		   ps.setString(7, null);
		   ps.setDate(8, null);
		   int k = ps.executeUpdate();
		   if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return false;
   }
   public Order getOrderById(int id) {
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
		   		+ "t2.username as username,t1.food_id as food_id,"
		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
		   		+ "t1.content as content,t1.comment_time as comment_time "
		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
		   		+ "t1.order_id = ?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		rs = ps.executeQuery();
		while(rs.next()) {
			int order_id = rs.getInt("order_id");
			int user_id = rs.getInt("user_id");
			String username = rs.getString("username");
			int food_id = rs.getInt("food_id");
			String foodname = rs.getString("foodname");
			int num = rs.getInt("num");
			double sum = rs.getDouble("sum");
			Timestamp d1 = rs.getTimestamp("ordertime");
			Date ordertime = new Date(d1.getTime());
		
			String suggesttime = rs.getString("suggesttime");
			String content = rs.getString("content");
			Timestamp d2 = rs.getTimestamp("comment_time");
			Date comment_time = null;
			if(d2!=null)
			comment_time = new Date(d2.getTime());
			Order order = new Order(order_id,user_id,username,food_id,foodname,
					num,sum,ordertime,suggesttime,content,comment_time);
			return order;
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return null;
   }
   public boolean update(Order order) {
	   Date d = new Date();
	   Timestamp t = new Timestamp(d.getTime());
	   sql = "update tb_order set content = ?,comment_time=? where order_id=?";
	   try {
		ps = conn.prepareStatement(sql);
		 ps.setString(1, order.getContent());
		 ps.setTimestamp(2, t);
		 ps.setInt(3, order.getOrder_id());
		 int k = ps.executeUpdate();
		 if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return false;
   }
   public boolean delete(int id) {
	   sql = "delete from tb_order where order_id=?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		int k = ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   public ArrayList<Order> getOrderByCondition(String condition,String value){
	   ArrayList<Order> list = new ArrayList<Order>();
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
		   		+ "t2.username as username,t1.food_id as food_id,"
		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
		   		+ "t1.content as content,t1.comment_time as comment_time "
		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
		   		+ condition+" like "+"'%"+value+"%'"; 
	   try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int order_id = rs.getInt("order_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				int num = rs.getInt("num");
				double sum = rs.getDouble("sum");
				Timestamp d1 = rs.getTimestamp("ordertime");
				Date ordertime = new Date(d1.getTime());
			
				String suggesttime = rs.getString("suggesttime");
				String content = rs.getString("content");
				Timestamp d2 = rs.getTimestamp("comment_time");
				Date comment_time = null;
				if(d2!=null)
				comment_time = new Date(d2.getTime());
				Order order = new Order(order_id,user_id,username,food_id,foodname,
						num,sum,ordertime,suggesttime,content,comment_time);
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   return list;
   }
   public ArrayList<Order> getOrderByFoodId(int id){
	   System.out.println("food:id" + id);
	   ArrayList<Order> list = new ArrayList<Order>();
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
		   		+ "t2.username as username,t1.food_id as food_id,"
		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
		   		+ "t1.content as content,t1.comment_time as comment_time "
		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
		   		+ "t1.food_id =? and t1.content !=''";
		   		 
	   try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				int order_id = rs.getInt("order_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				int num = rs.getInt("num");
				double sum = rs.getDouble("sum");
				Timestamp d1 = rs.getTimestamp("ordertime");
				Date ordertime = new Date(d1.getTime());
			
				String suggesttime = rs.getString("suggesttime");
				String content = rs.getString("content");
				Timestamp d2 = rs.getTimestamp("comment_time");
				Date comment_time = null;
				if(d2!=null)
				comment_time = new Date(d2.getTime());
				Order order = new Order(order_id,user_id,username,food_id,foodname,
						num,sum,ordertime,suggesttime,content,comment_time);
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   return list;
   }
   public ArrayList<Order> getOrderByUserIdUnComment(int id){
	   ArrayList<Order> list = new ArrayList<Order>();
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
		   		+ "t2.username as username,t1.food_id as food_id,"
		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
		   		+ "t1.content as content,t1.comment_time as comment_time "
		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
		   		+ "t1.user_id =? and t1.content =''";
		   		 
	   try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				int order_id = rs.getInt("order_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				int num = rs.getInt("num");
				double sum = rs.getDouble("sum");
				Timestamp d1 = rs.getTimestamp("ordertime");
				Date ordertime = new Date(d1.getTime());
			
				String suggesttime = rs.getString("suggesttime");
				String content = rs.getString("content");
				Timestamp d2 = rs.getTimestamp("comment_time");
				Date comment_time = null;
				if(d2!=null)
				comment_time = new Date(d2.getTime());
				Order order = new Order(order_id,user_id,username,food_id,foodname,
						num,sum,ordertime,suggesttime,content,comment_time);
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   return list;
   }
   public ArrayList<Order> getOrderByUserId(int id) {
	   ArrayList<Order> list = new ArrayList<Order>();
	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
		   		+ "t2.username as username,t1.food_id as food_id,"
		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
		   		+ "t1.content as content,t1.comment_time as comment_time "
		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
		   		+ "t1.user_id =?";
		   		 
	   try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				int order_id = rs.getInt("order_id");
				int user_id = rs.getInt("user_id");
				String username = rs.getString("username");
				int food_id = rs.getInt("food_id");
				String foodname = rs.getString("foodname");
				int num = rs.getInt("num");
				double sum = rs.getDouble("sum");
				Timestamp d1 = rs.getTimestamp("ordertime");
				Date ordertime = new Date(d1.getTime());
			
				String suggesttime = rs.getString("suggesttime");
				String content = rs.getString("content");
				Timestamp d2 = rs.getTimestamp("comment_time");
				Date comment_time = null;
				if(d2!=null)
				comment_time = new Date(d2.getTime());
				Order order = new Order(order_id,user_id,username,food_id,foodname,
						num,sum,ordertime,suggesttime,content,comment_time);
				list.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   return list;
   }
   public ArrayList<Order> getOrdersByUserIdCommented(int id){
        	 ArrayList<Order> list = new ArrayList<Order>();
      	   sql = "select t1.order_id as order_id,t1.user_id as user_id,"
      		   		+ "t2.username as username,t1.food_id as food_id,"
      		   		+ "t3.foodname as foodname,t1.num as num,t1.sum as sum,"
      		   		+ "t1.ordertime as ordertime,t1.suggesttime as suggesttime,"
      		   		+ "t1.content as content,t1.comment_time as comment_time "
      		   		+ "from tb_order t1,tb_user t2,tb_food t3 where "
      		   		+ "t1.user_id = t2.user_id and t1.food_id = t3.food_id and "
      		   		+ "t1.user_id =? and t1.content !=''";
      		   		 
      	   try {
      			ps = conn.prepareStatement(sql);
      			ps.setInt(1, id);
      			rs = ps.executeQuery();
      			while(rs.next()) {
      				int order_id = rs.getInt("order_id");
      				int user_id = rs.getInt("user_id");
      				String username = rs.getString("username");
      				int food_id = rs.getInt("food_id");
      				String foodname = rs.getString("foodname");
      				int num = rs.getInt("num");
      				double sum = rs.getDouble("sum");
      				Timestamp d1 = rs.getTimestamp("ordertime");
      				Date ordertime = new Date(d1.getTime());
      			
      				String suggesttime = rs.getString("suggesttime");
      				String content = rs.getString("content");
      				Timestamp d2 = rs.getTimestamp("comment_time");
      				Date comment_time = null;
      				if(d2!=null)
      				comment_time = new Date(d2.getTime());
      				Order order = new Order(order_id,user_id,username,food_id,foodname,
      						num,sum,ordertime,suggesttime,content,comment_time);
      				list.add(order);
      			}
      		} catch (SQLException e) {
      			// TODO Auto-generated catch block
      			e.printStackTrace();
      		}
      		   return list;
   }
   public boolean updateOrderByOrderId(int id,String content) {
	   sql = "update tb_order set content =?,comment_time=? where order_id = ?";
	   try {
		ps = conn.prepareStatement(sql);
		ps.setString(1, content);
		Date date = new Date();
		Timestamp time = new Timestamp(date.getTime());
		ps.setTimestamp(2, time);
		ps.setInt(3, id);
		int k = ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   return false;
   }
   public boolean deletePinglun(int id) {
	   sql = "update tb_order set content = '' where order_id=?";
	   try {
		ps = conn.prepareStatement(sql);
		 ps.setInt(1, id);
		int k =  ps.executeUpdate();
		if(k>0)return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	  return false;
   }
}
